import pymysql

from publicHandle.publicHandle import conn, cur, returnData


# 添加学生
def stuAdd(number, name, sex, age, grade, profession, credit, place):
    connection = conn()
    cursor = cur(connection)
    mysql_addStu = "insert into student(number ,name ,sex ,age ,grade ,profession ,credit ,place) values(%s,%s,%s,%s,%s,%s,%s,%s)"
    try:
        cursor.execute(mysql_addStu, (number, name, sex, age, grade, profession, credit, place))
        return returnData({"success": "true"})
    except:
        return returnData({"success": "false"}, "学生已存在")
    finally:
        connection.commit()
        connection.close()
        cursor.close()


# 删除学生信息
def stuDelete(number):
    connection = conn()
    cursor = cur(connection)
    mysql_uniqueStu = "delete from student where number = %s"
    try:
        cursor.execute(mysql_uniqueStu, (number))
        return returnData({"success": "true"})
    except:
        return returnData({"success": "false"})
    finally:
        connection.commit()
        connection.close()
        cursor.close()


# 查询所有学生信息
def stuSearchAll():
    connection = conn()
    cursor = cur(connection)
    mysql_allStu = "select number,name,sex,age,grade,profession,credit,place from student"
    try:
        cursor.execute(mysql_allStu)
        data = cursor.fetchall()
        temp = {}
        result = []
        if data != None:
            for i in data:
                temp["number"] = i[0]
                temp["name"] = i[1]
                temp["sex"] = i[2]
                temp["age"] = i[3]
                temp["grade"] = i[4]
                temp["profession"] = i[5]
                temp["credit"] = i[6]
                temp["place"] = i[7]
                result.append(temp.copy())
            return returnData(result)
        else:
            return returnData(result)
    except:
        return returnData()
    finally:
        connection.commit()
        connection.close()
        cursor.close()


# 查询具体学生信息
def stuSearch(number=None):
    connection = conn()
    cursor = cur(connection)
    mysql_uniqueStu = "select number,name,sex,age,grade,profession,credit,place from student where number = %s"
    try:
        cursor.execute(mysql_uniqueStu, (number))
        data = cursor.fetchone()
        arr = []
        temp = {}
        if data != None:
            temp["number"] = data[0]
            temp["name"] = data[1]
            temp["sex"] = data[2]
            temp["age"] = data[3]
            temp["grade"] = data[4]
            temp["profession"] = data[5]
            temp["credit"] = data[6]
            temp["place"] = data[7]
            arr.append(temp.copy())
        return returnData(arr)
    except:
        return returnData()
    finally:
        connection.commit()
        connection.close()
        cursor.close()


# 学生信息统计
def stuCount():
    connection = conn()
    cursor = cur(connection)
    mysql = "select count(number) from student"
    try:
        cursor.execute(mysql)
        count = cursor.fetchone()
        temp = {}
        temp["count"] = count[0]
        return returnData(temp)
    except:
        return returnData({}, "数据库请求错误", 400)
    finally:
        connection.commit()
        connection.close()
        cursor.close()


# 增加学分
def stuEditScore(studentNumber, scoreNumber):
    pass


# 图表统计显示
def staticsCount():
    connection = conn()
    cursor = cur(connection)
    mysqlCount = "select count(number) from student"
    mysqlGrade = "select count(*) from student where grade = %s"
    mysqlPro = "select profession,count(profession) from student GROUP BY profession"
    mysqlPlace = "select place,count(place) from student GROUP BY place"
    try:
        temp = {}
        # 计算学生总数
        cursor.execute(mysqlCount)
        temp["allCount"] = cursor.fetchone()[0]
        # 计算年级及年级人数
        cursor.execute(mysqlGrade, "大一")
        big1 = cursor.fetchone()
        cursor.execute(mysqlGrade, "大二")
        big2 = cursor.fetchone()
        cursor.execute(mysqlGrade, "大三")
        big3 = cursor.fetchone()
        cursor.execute(mysqlGrade, "大四")
        big4 = cursor.fetchone()
        temp["grade"] = {
            "big1": big1[0],
            "big2": big2[0],
            "big3": big3[0],
            "big4": big4[0]
        }
        # 专业及专业总人数
        cursor.execute(mysqlPro)
        profession = cursor.fetchall()
        temp["professional"] = dict(profession)
        # 省份分布情况
        province = [
            {
                "name": '云南省',
                "code": 530000,
                "value": 0,
            },
            {
                "name": '黑龙江省',
                "code": 230000,
                "value": 0,
            },
            {
                "name": '贵州省',
                "code": 520000,
                "value": 0,
            },
            {
                "name": '北京市',
                "code": 110000,
                "value": 0,
            },
            {
                "name": '河北省',
                "code": 130000,
                "value": 0,
            },
            {
                "name": '山西省',
                "code": 140000,
                "value": 0,
            },
            {
                "name": '吉林省',
                "code": 220000,
                "value": 0,
            },
            {
                "name": '宁夏回族自治区',
                "code": 640000,
                "value": 0,
            },
            {
                "name": '辽宁省',
                "code": 210000,
                "value": 0,
            },
            {
                "name": '海南省',
                "code": 460000,
                "value": 0,
            },
            {
                "name": '内蒙古自治区',
                "code": 150000,
                "value": 0,
            },
            {
                "name": '天津市',
                "code": 120000,
                "value": 0,
            },
            {
                "name": '新疆维吾尔自治区',
                "code": 650000,
                "value": 0,
            },
            {
                "name": '上海市',
                "code": 310000,
                "value": 0,
            },
            {
                "name": '陕西省',
                "code": 610000,
                "value": 0,
            },
            {
                "name": '甘肃省',
                "code": 620000,
                "value": 0,
            },
            {
                "name": '安徽省',
                "code": 340000,
                "value": 0,
            },
            {
                "name": '香港特别行政区',
                "code": 810000,
                "value": 0,
            },
            {
                "name": '广东省',
                "code": 440000,
                "value": 0,
            },
            {
                "name": '河南省',
                "code": 410000,
                "value": 0,
            },
            {
                "name": '湖南省',
                "code": 430000,
                "value": 0,
            },
            {
                "name": '江西省',
                "code": 360000,
                "value": 0,
            },
            {
                "name": '四川省',
                "code": 510000,
                "value": 0,
            },
            {
                "name": '广西壮族自治区',
                "code": 450000,
                "value": 0,
            },
            {
                "name": '江苏省',
                "code": 320000,
                "value": 0,
            },
            {
                "name": '澳门特别行政区',
                "code": 820000,
                "value": 0,
            },
            {
                "name": '浙江省',
                "code": 330000,
                "value": 0,
            },
            {
                "name": '山东省',
                "code": 370000,
                "value": 0,
            },
            {
                "name": '青海省',
                "code": 630000,
                "value": 0,
            },
            {
                "name": '重庆市',
                "code": 500000,
                "value": 0,
            },
            {
                "name": '福建省',
                "code": 350000,
                "value": 0,
            },
            {
                "name": '湖北省',
                "code": 420000,
                "value": 0,
            },
            {
                "name": '西藏自治区',
                "code": 540000,
                "value": 0,
            },
            {
                "name": '台湾省',
                "code": 710000,
                "value": 0,
            },
        ]
        cursor.execute(mysqlPlace)
        allPlace = cursor.fetchall()
        allPlaceDict = dict(allPlace)
        for place_item in province:
            for key,value in allPlaceDict.items():
                if place_item['name']== key:
                    place_item['value'] = value
        temp["province"] = province
        return returnData(temp)
    except pymysql.Error as e:
        return returnData({}, "数据库请求错误", 400)
    finally:
        connection.commit()
        connection.close()
        cursor.close()
